Hourly Electricity Consumption

Load packages

library(tidyverse)

── Attaching core tidyverse packages ────── tidyverse 2.0.0 ──
✔ dplyr     1.1.2     ✔ readr     2.1.4
✔ forcats   1.0.0     ✔ stringr   1.5.0
✔ ggplot2   3.4.2     ✔ tibble    3.2.1
✔ lubridate 1.9.2     ✔ tidyr     1.3.0
✔ purrr     1.0.1     ── Conflicts ──────────────────────── tidyverse_conflicts() ──
✖ dplyr::filter() masks stats::filter()
✖ dplyr::lag()    masks stats::lag()
ℹ Use the ]8;;http://conflicted.r-lib.org/conflicted package]8;; to force all conflicts to become errors

library(lubridate)
library(hms)

Attaching package: ‘hms’

The following object is masked from ‘package:lubridate’:

    hms

#library(googlesheets4)
library(readxl)

#gs4_deauth()
#ppl_15mins <- read_sheet("https://docs.google.com/spreadsheets/d/1uv5SBYklQ-bArCnWPVG1hOvKu3fpgD1sgU8bTZ8sR5o/edit?usp=sharing")
#add column types


col_datatypes <- c('numeric','numeric','date','text',rep('numeric',99))

#?read_excel

hourly1 <- read_excel("Hourly Usage 220326 to 220624.xlsx", col_types = col_datatypes)

Warning: Expecting numeric in A277 / R277C1: got 'The information contained in this file is intended for the confidential use by the customer and third parties authorized by the customer to receive the information. Any unauthorized use is prohibited.'

hourly2 <- read_excel("Hourly Usage 220625 to 230623.xlsx", col_types = col_datatypes)

Warning: Expecting numeric in A1096 / R1096C1: got 'The information contained in this file is intended for the confidential use by the customer and third parties authorized by the customer to receive the information. Any unauthorized use is prohibited.'

hourly3 <- read_excel("PPL 230624 to 240509.xlsx", col_types = col_datatypes)

Warning: Expecting numeric in A967 / R967C1: got 'The information contained in this file is intended for the confidential use by the customer and third parties authorized by the customer to receive the information. Any unauthorized use is prohibited.'

hourly1

hourly2

hourly3

ppl_15mins <- bind_rows(hourly1,list(hourly2,hourly3))

ppl_15mins %>% arrange(desc(Date), `Read Type`)

NA
NA

#ppl_15mins <- ppl_15mins %>% rename(date = Date)

hourly_ppl_pivot <- ppl_15mins %>% 
  rename(date = Date) %>% 
  pivot_longer(!c("Account Number", "Meter Number", date, "Read Type", Min, Max, Total), names_to = "time", values_to = "kWh") 

#ppl_15mins %>%  mutate(date = as_date(Date)) 

rename(ppl_15mins, date = Date)

#rm(hourly_pivot)

hourly_ppl_pivot <- hourly_ppl_pivot %>% 
  mutate(time = parse_time(time, '%H:%M %p'), month = month(date, label=TRUE), year = year(date), yday = yday(date), wday = wday(date, label=TRUE))

(hourly_ppl_net <- hourly_ppl_pivot %>% 
  filter(`Read Type` == "kWh Net"))

Import solar production from another notebook (EnphaseHourly.Rmd)

ggplot(hourly_production, aes(datetime, energy_produced_Wh)) +
  geom_point()

ggplot(hourly_production, aes(time, energy_produced_Wh)) +
  theme(axis.text.x = element_text(angle = 90)) +
  geom_point()

Net + Produced = Consumedggplot(hourly_production, aes(datetime, energy_produced_Wh)) +

geom_point()

ggplot(hourly_production, aes(time, energy_produced_Wh)) +

theme(axis.text.x = element_text(angle = 90)) +

geom_point()

# hourly_ppl_net <- hourly_ppl_net %>% mutate(date = as_date(date))

hourly_ppl_net %>% arrange(desc(date))

hourly_production %>% arrange(desc(date))

(hourly_electricity <- hourly_ppl_net %>% 
    inner_join(hourly_production, by = join_by(date,time))  %>% 
    mutate(consumed_kWh = kWh + energy_produced_Wh/1000, produced_kWh = energy_produced_Wh/1000)  %>% 
    rename(net_kWh = kWh) %>% 
    select(datetime, date, time, net_kWh, produced_kWh, consumed_kWh))

NA

ggplot(hourly_electricity, aes(x=time)) +
  geom_point(aes(y=consumed_kWh,color="red")) 

ggplot(hourly_electricity, aes(x=datetime)) +
  geom_point(aes(y=consumed_kWh,color="red")) 

hourly_electricity %>% summarize(min_date = min(date), max_date = max(date))

electricity_by_time <- hourly_electricity %>% 
  filter(date <= as.POSIXct('12/31/2022 00:00',format="%m/%d/%Y %H:%M",tz=Sys.timezone()) ) %>% 
  group_by(time) %>% 
  summarize(produced_kWh = mean(produced_kWh), consumed_kWh = mean(consumed_kWh), net_kWh = mean(net_kWh)) %>% 
  arrange(time)

electricity_by_time

ggplot(electricity_by_time, aes(x=time)) +
  geom_point(aes(y=consumed_kWh, color="Consumed")) +
  geom_point(aes(y=net_kWh, color="Net")) +
  geom_point(aes(y=produced_kWh*-1, color="Produced")) +
  labs(colour="",x="Time of Day",y="Electricity Consumption (kWh)")+
  scale_color_manual(values = c("red","black","green")) +
  ggtitle("Home Electricity in 15-Minute Intervals (Apr 15 - Dec 31, 2022)")

electricity_by_month_time <- hourly_electricity %>% 
  mutate(month = as_factor(month(date))) %>% 
  filter(date <= as.POSIXct('12/31/2022 00:00',format="%m/%d/%Y %H:%M",tz=Sys.timezone()) ) %>% 
  group_by(month, time) %>% 
  summarize(produced_kWh = mean(produced_kWh), consumed_kWh = mean(consumed_kWh), net_kWh = mean(net_kWh)) %>% 
  arrange(month, time)

`summarise()` has grouped output by 'month'. You can override using the `.groups` argument.

ggplot(electricity_by_month_time, aes(time, consumed_kWh, group=month, color=month)) +
  geom_point() +
  ggtitle("Home Electricity in 15-Minute Intervals (Apr 15 - Dec 31, 2022)") +
  ylab("Net Electricity Consumption (kWh)")

NA
NA

Example day with EV charging

#hourly_electricity

march23 <- hourly_electricity %>% 
  filter(date >= "2023-04-01" & date < "2023-04-06")

ggplot(march23) +
  geom_point(aes(datetime,net_kWh))

ggplot(march23) +
  geom_point(aes(time,net_kWh, color = factor(date)))